Public Class frmQuery
    Inherits System.Windows.Forms.Form

    'Public MyDataTable As clsDataTable
    Friend MyFullTableName As String

    ''' <summary>
    ''' If true, will automatically clear all contents each time is displayed (will not restore prior query from registry);
    ''' Would be better if could show current filter in effect for the browser, but that is not feasible;
    ''' Set this before calling ShowDialog
    ''' </summary>
    Friend Property IsBrowserFilter As Boolean = False

    Friend Function BuildSQL() As String

        'fix wildcards for all text fields
        txtStationID.Text = fixLikeWildcards(txtStationID.Text)
        txtPCode.Text = fixLikeWildcards(txtPCode.Text)
        txtCCode.Text = fixLikeWildcards(txtCCode.Text)
        txtRCode.Text = fixLikeWildcards(txtRCode.Text)
        txtQCode.Text = fixLikeWildcards(txtQCode.Text)
        txtLCode.Text = fixLikeWildcards(txtLCode.Text)
        txtSCode.Text = fixLikeWildcards(txtSCode.Text)
        txtOwner.Text = fixLikeWildcards(txtOwner.Text)

        Dim WhereClause As String = ""

        For i As Integer = 1 To 15
            With CType(Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtLEWPct, txtDepthM, txtRCode, txtQCode, txtLCode, txtSCode, txtValidated, txtTrackID, txtOwner, txtCreateUpdate), TextBox)
                Dim FieldName As String = "Dta." & Choose(i, "Station_ID", "Date_Time", "PCode", "CCode", "Result", "LEW_Pct", "Depth_M", "RCode", "QCode", "LCode", "SCode", "Validated", "Track_ID", "Owner", "Create_Update")
                Dim Value As String = .Text.Trim
                Select Case i
                    Case 2, 15 'this field may have some fancy date extraction text like "EXTRACT(YEAR from Date_time)=2009"
                        If .Text.ToUpper.Contains("DATE_TIME") Then
                            Value = System.Text.RegularExpressions.Regex.Replace(.Text, "Date_Time", FieldName)
                            'Value = Value.ToUpper.Replace("DATE_TIME", FieldName)
                            FieldName = ""
                        ElseIf IsDate(.Text) Then
                            Value = String.Format("BETWEEN {0} AND {1}", Project.DB.DateTime(CDate(.Text)), Project.DB.DateTime(CDate(.Text).AddDays(1).AddMinutes(-1)))
                        End If
                    Case 5, 6, 7 'this field may just contain numeric value; need to convert to invariant number format and insert =
                        If IsNumeric(.Text) Then
                            Value = "= " & Double.Parse(.Text).ToString("N3", System.Globalization.CultureInfo.InvariantCulture)
                        End If
                    Case 12 'will contain = True or = False
                        Dim b As Boolean = False
                        If Boolean.TryParse(.Text.Replace("=", "").Trim, b) Then
                            Value = "= " & Project.DB.Logical(b)
                            If Not b Then Value &= $" OR  {FieldName} IS NULL"
                        End If
                End Select
                If Value = "ALL" Or Value = "" Then
                    'leave clause empty for this field
                ElseIf Value.StartsWith("IN", StringComparison.OrdinalIgnoreCase) And Value.Contains("%") Then
                    'replace IN clause having wildcards with multiple LIKEs
                    Dim ar() As String = Value.Replace("IN (", "").Replace(")", "").Split(",")
                    Dim LikeClause As String = ""
                    For Each s As String In ar
                        LikeClause &= IIf(LikeClause = "", "", " OR ") & FieldName & " LIKE " & s
                    Next
                    WhereClause &= IIf(WhereClause = "", "(", " AND (") & LikeClause & ")"
                Else
                    'prepend field name only and assume rest of field is formatted correctly
                    WhereClause &= IIf(WhereClause = "", "", " AND ") & FieldName & " " & Value
                End If
                WhereClause = WhereClause.Replace("OR IN (", $"OR {FieldName} IN (")
                If Value.EndsWith("OR IS NULL") Then WhereClause = WhereClause.Replace("OR IS NULL", " OR " & FieldName & " IS NULL")
            End With
        Next

        If WhereClause <> "" Then WhereClause = " WHERE " & WhereClause

        Return MainForm.SelectBuildQuery(Me.cboOption.SelectedIndex, WhereClause)
    End Function

    Private Function fixLikeWildcards(ByVal s As String) As String
        Return s.Replace("..", "%").Replace("*", "%").Replace("@", "_").Replace("?", "_")
    End Function

    Private Sub btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStationID.Click, btnCCode.Click, btnDateTime.Click, btnRCode.Click, btnResult.Click, btnPCode.Click, btnDepthM.Click, btnLEWPct.Click, btnCreateUpdate.Click, btnTrackID.Click, btnValidated.Click, btnSCode.Click, btnLCode.Click, btnQCode.Click, btnOwner.Click
        Try
            Dim AssistType As frmQueryAssist.enumAssist, FieldName As String
            If sender Is btnStationID Then
                AssistType = frmQueryAssist.enumAssist.StationID
                FieldName = "Station_ID"
            ElseIf sender Is btnDateTime Then
                AssistType = frmQueryAssist.enumAssist.DateTimeValue
                FieldName = "Date_Time"
            ElseIf sender Is btnPCode Then
                AssistType = frmQueryAssist.enumAssist.PCode
                FieldName = "PCode"
            ElseIf sender Is btnCCode Then
                AssistType = frmQueryAssist.enumAssist.CCode
                FieldName = "CCode"
            ElseIf sender Is btnResult Then
                AssistType = frmQueryAssist.enumAssist.RealValue
                FieldName = "Result"
            ElseIf sender Is btnLEWPct Then
                AssistType = frmQueryAssist.enumAssist.RealValue
                FieldName = "LEW_Pct"
            ElseIf sender Is btnDepthM Then
                AssistType = frmQueryAssist.enumAssist.RealValue
                FieldName = "Depth_M"
            ElseIf sender Is btnRCode Then
                AssistType = frmQueryAssist.enumAssist.RCode
                FieldName = "RCode"
            ElseIf sender Is btnQCode Then
                AssistType = frmQueryAssist.enumAssist.QCode
                FieldName = "QCode"
            ElseIf sender Is btnLCode Then
                AssistType = frmQueryAssist.enumAssist.Validate
                FieldName = "LCode"
            ElseIf sender Is btnSCode Then
                AssistType = frmQueryAssist.enumAssist.Validate
                FieldName = "SCode"
            ElseIf sender Is btnValidated Then
                'AssistType = frmQueryAssist.enumAssist.StringValue
                'FieldName = "Validated"
                WarningMsg("Enter True, T, 1 or False, F, 0 in textbox to select validated or unvalidated records, respectively.")
                Exit Sub
            ElseIf sender Is btnTrackID Then
                AssistType = frmQueryAssist.enumAssist.RealValue
                FieldName = "Track_ID"
            ElseIf sender Is btnOwner Then
                AssistType = frmQueryAssist.enumAssist.StringValue
                FieldName = "Owner"
            ElseIf sender Is btnCreateUpdate Then
                AssistType = frmQueryAssist.enumAssist.DateTimeValue
                FieldName = "Create_Update"
            Else
                Exit Sub
            End If

            Dim tblname As String = MyFullTableName
            If cboOption.SelectedIndex <> clsWRDB.enumSelectOption.SelectActiveCreate Then tblname = "" 'only allow "display selected" if selecting from a single table
            With New frmQueryAssist(tblname, FieldName, AssistType)
                If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                    Dim txt As TextBox = Controls.Find(CType(sender, Button).Name.Replace("btn", "txt"), True)(0)
                    txt.Text = .QueryString
                    ResizeTextbox(txt)
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        DialogResult = Windows.Forms.DialogResult.Cancel
        Close()
    End Sub

    Private Sub btnExecute_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExecute.Click
        Try
            If IsBrowserFilter Then 'save values to memory associated 
                DialogResult = Windows.Forms.DialogResult.OK
                Close()
            Else 'save settings to registry 
                SaveControlValues(REGAPP, Me)
                MainForm.SelectData(BuildSQL, Me.cboOption.SelectedIndex, False)
            End If

        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        frmInfoMsg.ShowInfoMsg("Parameter Queries", "You are about to save this query so it can be reused at a later time.\n\nNote that WRDB can also embed parameters in your queries to make it more convenient to reuse them. For each named parameter you place into your query enclosed in brackets (e.g., WHERE PCode = '{Enter Desired Parameter Code}'), a prompt will appear when you rerun the query which allows you to specify the desired value at that time.")

        With New SaveFileDialog
            .AddExtension = True
            .CheckPathExists = True
            .SupportMultiDottedExtensions = False
            .DefaultExt = "SQL"
            .Filter = "Saved SQL Query File (*.sql)|*.sql"
            .InitialDirectory = TestNull(Project.SQLDir, MyDataPath)
            If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                Dim sw As New IO.StreamWriter(.FileName, False)
                sw.Write(BuildSQL)
                sw.Close()
                sw.Dispose()
                sw = Nothing
            End If
        End With
    End Sub

    Private Sub btnSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
        SelectAll()
    End Sub

    Private Sub btnShowSQL_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShowSQL.Click
        MessageBox.Show(StringFormat(FormatSQL(BuildSQL)), "SQL String", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End Sub

    Private Sub frmQuery_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cboOption.SelectedIndex = 0

        If Not IsBrowserFilter Then 'retrieve settings from registry associated with last query
            SelectAll()
            GetControlValues(REGAPP, Me)
        End If

        cboOption.SelectedIndex = 0

        'see if any extra fields normally only shown when "More" is clicked are non-empty; if so, show more
        Dim IsMore As Boolean = False

        For i As Integer = 1 To 15
            Dim txt As TextBox = Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtLEWPct, txtDepthM, txtRCode, txtQCode, txtLCode, txtSCode, txtValidated, txtTrackID, txtOwner, txtCreateUpdate)
            With txt
                If .Text = "" Then .Text = "ALL"
                If i >= 6 And .Text.ToUpper <> "ALL" Then IsMore = True
                ResizeTextbox(txt)
            End With
        Next
        'Height = 276 'extra fields will be hidden by default

        If IsMore Then btnMoreLess.PerformClick()

    End Sub

    Private Sub ParseString(ByRef txt As TextBox)

        'when leave the field, take list that user typed (or was inserted by
        'assistant) and create proper SQL where clause

        'if assistant used tick marks, assume string is already properly formatted

        txt.Text = fixLikeWildcards(txt.Text)

        If txt.Text.Trim = "" Or txt.Text.ToUpper.Trim = "ALL" Then
            txt.Text = "ALL"
        Else
            If Not (txt.Text.Contains("'") Or txt.Text.ToUpper.Contains("NULL")) Then 'no quotes, so try to fix it up
                Dim ar() As String = txt.Text.Split(",")
                If ar.Length = 1 Then 'is single item
                    If txt.Text.Contains("%") Or txt.Text.Contains("_") Then
                        txt.Text = "LIKE '" & ar(0).ToUpper & "'"
                    Else
                        txt.Text = "= '" & ar(0).ToUpper & "'"
                    End If
                Else 'is multiple items, make "In" list
                    'Oracle cannot handle more than 1000 items in a list; break into multiple lists
                    Dim MaxLength = 1000
                    If ar.Length > 1000 Then txt.Text = "(" 'need extra set of parens so full SQL correctly handles (A or B or C) and D
                    txt.Text &= "IN ("
                    For i As Int16 = 0 To ar.Length - 1
                        If i - 1 > 0 AndAlso i Mod MaxLength = 0 Then
                            txt.Text &= ") OR IN ("
                        End If
                        txt.Text &= IIf(txt.Text.EndsWith("("), "'", ", '") & ar(i).Trim.ToUpper & "'"
                    Next
                    txt.Text &= ")"
                    If ar.Length > 1000 Then txt.Text &= ")"
                End If
            End If
        End If
    End Sub

    Private Sub SelectAll()
        For i As Integer = 1 To 15
            Dim txt As TextBox = Choose(i, txtStationID, txtDateTime, txtPCode, txtCCode, txtResult, txtLEWPct, txtDepthM, txtRCode, txtQCode, txtLCode, txtSCode, txtValidated, txtTrackID, txtOwner, txtCreateUpdate)
            With txt
                .Text = "ALL"
                ResizeTextbox(txt)
            End With
        Next
    End Sub

    'now use my own context menu

    'Private Sub txt_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCCode.TextChanged, txtDateTime.TextChanged, txtOther.TextChanged, txtPCode.TextChanged, txtRCode.TextChanged, txtResult.TextChanged, txtStationID.TextChanged
    '    'if pasted list from spreadsheet (crlf or tab separated), convert to commas
    '    'for this to work, textbox multiline property must be true
    '    With CType(sender, TextBox)
    '        If .Text.EndsWith(vbCrLf) Then .Text = .Text.Substring(0, .Text.Length - 2)
    '        If .Text.EndsWith(vbCr) Then .Text = .Text.Substring(0, .Text.Length - 1)
    '        .Text = .Text.Replace(vbCrLf, ", ").Replace(vbCr, ", ").Replace(vbTab, ", ")
    '    End With
    'End Sub

    'note: old VB6 behavior of selecting entire text when first clicked is not implemented (is not standard for WinXP anymore, and is difficult to implement)

    Friend Property FullTableName() As String
        Get
            Return MyFullTableName
        End Get
        Set(ByVal Value As String)
            MyFullTableName = Value
        End Set
    End Property

    Private Sub txtString_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtCCode.Validated, txtRCode.Validated, txtStationID.Validated, txtPCode.Validated, txtQCode.Validated, txtLCode.Validated, txtSCode.Validated, txtOwner.Validated
        Dim txt As TextBox = sender
        ParseString(txt)
        ResizeTextbox(txt)
    End Sub

    Private Sub ResizeTextbox(txt As TextBox)
        If txt.CreateGraphics.MeasureString(txt.Text, txt.Font).Width > txt.Width Then 'for long strings automatically expand to hold
            txt.WordWrap = True
            txt.Multiline = True
            txt.ScrollBars = ScrollBars.Vertical
            txt.Height = Math.Min(100, txt.CreateGraphics.MeasureString(txt.Text, txt.Font, txt.Width, New StringFormat(0)).Height * 1.1)
        Else
            txt.WordWrap = False
            txt.Multiline = False
            txt.ScrollBars = ScrollBars.None
            txt.Height = 24
        End If
    End Sub

    Private Sub txtLogical_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtValidated.Validated
        With CType(sender, TextBox)
            'convert entry to True or False; provider conversion takes place in BuildSQL
            If .Text.Trim = "" Or .Text.Trim.ToUpper = "ALL" Then
                .Text = "ALL"
            ElseIf .Text.Replace("=", "").Trim.ToUpper.StartsWith("T") Or Val(.Text) = 1 Then
                .Text = "= True"
            ElseIf .Text.Replace("=", "").Trim.ToUpper.StartsWith("F") Or Val(.Text) = 0 Then
                .Text = "= False"
            End If
        End With
    End Sub

    Private Sub txtNumber_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtResult.Validated, txtDepthM.Validated, txtLEWPct.Validated, txtTrackID.Validated
        'if only entered number, assume want = operator (note: this converts user's number format into invariant format needed for SQL, so format may change)
        With CType(sender, TextBox)
            If IsNumeric(.Text) Then 'this now happens in BuildSQL
                '.Text = "= " & Double.Parse(.Text).ToString("N3", System.Globalization.CultureInfo.InvariantCulture)
            ElseIf .Text.Trim = "" Or .Text.Trim.ToUpper = "ALL" Then
                .Text = "ALL"
            End If
        End With
    End Sub

    Private Sub txtDate_Validated(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtDateTime.Validated, txtCreateUpdate.Validated
        'if only entered date, assume want all values on that date (note: this converts user's date format into invariant format needed for SQL, so format may change)
        With CType(sender, TextBox)
            If IsDate(.Text) Then 'this now happens in BuildSQL
                '.Text = String.Format("BETWEEN {0} AND {1}", Project.DB.DateTime(CDate(.Text)), Project.DB.DateTime(CDate(.Text).AddDays(1).AddMinutes(-1)))
            ElseIf .Text.Trim = "" Or .Text.Trim.ToUpper = "ALL" Then
                .Text = "ALL"
            End If
        End With

    End Sub

    Private Sub btnHelp_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnHelp.Click
        ShowHelp(Me, "16")
    End Sub

    Private Sub btnGIS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGIS.Click
        Try
            Dim lst As New Generic.List(Of String)
            If IsMWForm Then
                'lst = CType(MainForm, frmMainMW).SelectedStations
            Else
                MainForm.CheckGisData()
                With New WRDB.GIS.frmGIS(Project, ActiveTableName, clsProject.TempData, GIS.frmGIS.enumMode.SelectStations, MainWRDB.WRDBStationTypes, Project.GisBasemapStations)
                    If .ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
                        lst = .SelectedStations
                    End If
                    .Dispose()
                End With
            End If

            If lst.Count > 0 Then
                Dim s As String = ""
                For Each sta As String In lst
                    s &= IIf(s = "", "", ", ") & "'" & sta & "'"
                Next
                txtStationID.Text = String.Format("IN ({0})", s)
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub ContextMenuStrip1_ItemClicked(ByVal sender As Object, ByVal e As System.Windows.Forms.ToolStripItemClickedEventArgs) Handles ContextMenuStrip1.ItemClicked
        Try
            Dim txt As TextBox = CType(sender, ContextMenuStrip).SourceControl
            With txt
                If e.ClickedItem Is mnuCut Then
                    My.Computer.Clipboard.SetText(.SelectedText, TextDataFormat.Text)
                    .Text = .Text.Remove(.SelectionStart, .SelectionLength)
                ElseIf e.ClickedItem Is mnuCopy Then
                    My.Computer.Clipboard.SetText(.SelectedText, TextDataFormat.Text)
                ElseIf e.ClickedItem Is mnuPaste Then
                    FixPasteString(txt)
                ElseIf e.ClickedItem Is mnuDelete Then
                    .Text = .Text.Remove(.SelectionStart, .SelectionLength)
                ElseIf e.ClickedItem Is mnuSelectAll Then
                    .SelectAll()
                End If
            End With
        Catch ex As Exception
            ErrorMsg(, ex)
        End Try
    End Sub

    Private Sub txt_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtCCode.KeyDown, txtDateTime.KeyDown, txtPCode.KeyDown, txtRCode.KeyDown, txtResult.KeyDown, txtStationID.KeyDown, txtDepthM.KeyDown, txtLEWPct.KeyDown, txtCreateUpdate.KeyDown, txtTrackID.KeyDown, txtOwner.KeyDown, txtValidated.KeyDown, txtSCode.KeyDown, txtLCode.KeyDown, txtQCode.KeyDown
        If e.Control And e.KeyCode = Keys.V Then 'paste shortcut
            FixPasteString(sender)
            e.Handled = True
            My.Computer.Clipboard.Clear() 'this is necessary because for some reason will still try to paste again
        End If
    End Sub

    Private Sub txt_KeyPress(sender As Object, e As KeyPressEventArgs) Handles txtResult.KeyPress, txtDepthM.KeyPress, txtLEWPct.KeyPress
        'when type decimal point, like Excel always replace with comma if that is what is dictated by culture setting
        If e.KeyChar = "." Then
            e.KeyChar = Globalization.CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator
        End If
    End Sub

    Private LastFocused As Control = Nothing

    Private Sub txt_Enter(sender As Object, e As System.EventArgs) Handles txtCCode.Enter, txtCreateUpdate.Enter, txtDateTime.Enter, txtDepthM.Enter, txtLCode.Enter, txtLEWPct.Enter, txtOwner.Enter, txtPCode.Enter, txtQCode.Enter, txtRCode.Enter, txtResult.Enter, txtSCode.Enter, txtStationID.Enter, txtTrackID.Enter, txtValidated.Enter
        If MouseButtons = Windows.Forms.MouseButtons.None Then LastFocused = sender
    End Sub

    Private Sub txt_Leave(sender As Object, e As System.EventArgs) Handles txtCCode.Leave, txtCreateUpdate.Leave, txtDateTime.Leave, txtDepthM.Leave, txtLCode.Leave, txtLEWPct.Leave, txtOwner.Leave, txtPCode.Leave, txtQCode.Leave, txtRCode.Leave, txtResult.Leave, txtSCode.Leave, txtStationID.Leave, txtTrackID.Leave, txtValidated.Leave
        LastFocused = Nothing
    End Sub

    Private Sub txt_MouseUp(sender As Object, e As System.Windows.Forms.MouseEventArgs) Handles txtCCode.MouseUp, txtCreateUpdate.MouseUp, txtDateTime.MouseUp, txtDepthM.MouseUp, txtLCode.MouseUp, txtLEWPct.MouseUp, txtOwner.MouseUp, txtPCode.MouseUp, txtQCode.MouseUp, txtRCode.MouseUp, txtResult.MouseUp, txtSCode.MouseUp, txtStationID.MouseUp, txtTrackID.MouseUp, txtValidated.MouseUp
        With CType(sender, TextBox)
            If LastFocused IsNot sender AndAlso .SelectionLength = 0 Then .SelectAll()
        End With
        LastFocused = sender
    End Sub

    ''' <summary>
    ''' Replace selected text in textbox with contents of clipboard; because might paste from Excel where the values are delimited by tabs or returns, replace with commas.
    ''' </summary>
    ''' <param name="TxtBox">Active textbox to paste to</param>
    Private Sub FixPasteString(ByRef TxtBox As TextBox)
        With TxtBox
            Dim i As Integer = .SelectionStart
            .Text = .Text.Remove(.SelectionStart, .SelectionLength)
            Dim text As String = My.Computer.Clipboard.GetText(TextDataFormat.Text)
            If text.EndsWith(vbCrLf) Then text = text.Substring(0, text.Length - 2)
            If text.EndsWith(vbCr) Then text = text.Substring(0, text.Length - 1)
            text = text.Replace(vbCrLf, ", ").Replace(vbCr, ", ").Replace(vbTab, ", ")
            .Text = .Text.Insert(.SelectionStart, text).Trim
            If .Text.EndsWith(",") Then .Text = .Text.Substring(0, .Text.Length - 1)
            If i >= 0 And i <= .Text.Length Then .SelectionStart = i
        End With
    End Sub

    Private Sub btnMoreLess_Click(sender As System.Object, e As System.EventArgs) Handles btnMoreLess.Click
        If tblMore.Visible Then
            'Me.Height -= tblMore.Height
            tblMore.Visible = False
            btnMoreLess.Text = "&More >>"
        Else
            tblMore.Visible = True
            'Me.Height += tblMore.Height
            btnMoreLess.Text = "<< &Less"
        End If
    End Sub

End Class
